package com.etc.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import com.etc.dao.BusDao;
import com.etc.dao.SiteDao;
import com.etc.entity.Bus;
import com.etc.entity.BusInfo;
import com.etc.entity.Site;
import com.etc.entity.User;
import com.etc.util.DBUtil;
import com.etc.util.PageData;

public class BusDaoImpl implements BusDao{

	//实现车辆的方法
	@Override
	public boolean insertBus(Bus bus) {
		if(bus!=null) {
			String sql="insert into bus values(null,?,?,?,?)";
			return DBUtil.executeUpdate(sql, bus.getB_begin(),bus.getB_end(),bus.getB_price(),bus.getM_id());	
		}	
		return false;
	}

	//实现删除方法
	@Override
	public boolean delBus(int id) {
		if(id>0) {
			String sql="delete from bus where b_id=?";
			return DBUtil.executeUpdate(sql, id);
		}
		return false;
	}

	//实现修改的方法
	@Override
	public boolean updateBus(BusInfo busInfo) {
		String b_begin = busInfo.getB_begin();
		String b_end = busInfo.getB_end();
		String[] split = busInfo.getData().split("-");
		System.out.println(split);
		int b_begin1 = querySiteId(b_begin);
		int b_end1 = querySiteId(b_end);
		int m_id = 0;
		CachedRowSet crs = DBUtil.execQuery("select m_id from moments where m_begin=? and m_end=?",split[0],split[1]);
		try {
			while (crs.next()) {
				m_id = crs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(busInfo!=null) {
			String sql="update bus set b_begin=?,b_end=?,b_price=?,m_id=? where b_id=?";
			return DBUtil.executeUpdate(sql, b_begin1,b_end1,busInfo.getB_price(),m_id,busInfo.getB_id());
		}
		return false;
	}

	//实现车辆查询
	@Override
	public List<Bus> queryBus() {
		String sql="select * from bus";
		CachedRowSet crs = DBUtil.execQuery(sql);
		Bus bus=null;
		List<Bus> list=new ArrayList<Bus>();
		try {
			while(crs.next()) {
				int b_id = crs.getInt(1);
				int b_begin = crs.getInt(2);
				int b_end = crs.getInt(3);
				int b_price = crs.getInt(4);
				int m_id = crs.getInt(5);
				bus = new Bus(b_id, b_begin, b_end, b_price, m_id);
				list.add(bus);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if(null!=crs) {
				try {
					crs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return list;
	}
	//车辆信息
	@Override
	public PageData queryBusByLikeAndLimit(int pageNo, int pageSize) {
		ArrayList<BusInfo> list = new ArrayList<>();
		BusInfo busInfo = new BusInfo();
		String sql = "select * from bus limit ?,?";
		String b_begin1=null;
		String b_end1=null;
		String data=null;
		CachedRowSet crs = DBUtil.execQuery(sql,(pageNo-1)*pageSize,pageSize);
		try {
			while(crs.next()) {
				int b_id=crs.getInt(1);
				int b_begin=crs.getInt(2);
				int b_end=crs.getInt(3);
				int b_price=crs.getInt(4);
				int m_id=crs.getInt(5);
				CachedRowSet crs1 = DBUtil.execQuery("select s_name from site where s_id=?",b_begin);
				while (crs1.next()) {
					b_begin1 = crs1.getString(1);
				}
				CachedRowSet crs2 = DBUtil.execQuery("select s_name from site where s_id=?",b_end);
				while (crs2.next()) {
					b_end1 = crs2.getString(1);
				}
				CachedRowSet crs3 = DBUtil.execQuery("select m_begin,m_end from moments where m_id=?",m_id);
				while (crs3.next()) {
					String m_begin = crs3.getString(1);
					String m_end = crs3.getString(2);
					data = m_begin+"-"+m_end;
				}
				busInfo = new BusInfo(b_id, b_begin1, b_end1, b_price, data);
				list.add(busInfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		int totalCount=0;
		CachedRowSet crs4 = DBUtil.execQuery("select count(*) from bus");
		try {
			while(crs4.next()) {
				totalCount=crs4.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageData pageData = new PageData(list, pageNo, pageSize, totalCount);
		return pageData;
	}

	@Override
	public int querySiteId(String name) {
		int s_id = 0;
		CachedRowSet crs = DBUtil.execQuery("select s_id from site where s_name=?",name);
		try {
			while(crs.next()) {
				s_id = crs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return s_id;
	}

	@Override
	public List<BusInfo> queryBusIdAndEnd(String name) {
		int id = querySiteId(name);
		ArrayList<BusInfo> list = new ArrayList<>();
		CachedRowSet crs = DBUtil.execQuery("select b_id from bussite where s_id=?",id);
		try {
			while(crs.next()) {
				int b_id = crs.getInt(1);
				String b_end = null;
				CachedRowSet crs1 = DBUtil.execQuery("select b_end from bus where b_id=?",b_id);
				while(crs1.next()) {
					int end = crs1.getInt(1);
					CachedRowSet crs2 = DBUtil.execQuery("select s_name from site where s_id=?",end);
					while(crs2.next()) {
						b_end = crs2.getString(1);
					}
				}
				BusInfo busInfo = new BusInfo(b_id,b_end);
				list.add(busInfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<String> queryBusRoute(int b_id) {
		CachedRowSet crs = DBUtil.execQuery("select b.s_name from bussite a inner join site b on a.s_id=b.s_id where a.b_id=? order by a.s_location;",b_id);
		ArrayList<String> list = new ArrayList<>();
		try {
			while(crs.next()) {
				String s_name = crs.getString(1);
				list.add(s_name);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<String> queryBusRoutes(String begin, String end) {
		int id1 = querySiteId(begin);
		int id2 = querySiteId(end);
		CachedRowSet crs1 = DBUtil.execQuery("select b_id from bussite where s_id=?",id1);
		CachedRowSet crs2 = DBUtil.execQuery("select b_id from bussite where s_id=?",id2);
		try {
			while (crs1.next()) {
				int b_id1 = crs1.getInt(1);
				CachedRowSet crs = DBUtil.execQuery("select b.s_name from bussite a inner join site b on a.s_id=b.s_id where a.b_id=? order by a.s_location;",b_id1);
				ArrayList<String> list1 = new ArrayList<>();
				while (crs.next()) {
					String s_name = crs.getString(1);
					list1.add(s_name);
				}
			}
			while (crs2.next()) {
				int b_id2 = crs1.getInt(1);
				CachedRowSet crs = DBUtil.execQuery("select b.s_name from bussite a inner join site b on a.s_id=b.s_id where a.b_id=? order by a.s_location;",b_id2);
				ArrayList<String> list2 = new ArrayList<>();
				while (crs.next()) {
					String s_name = crs.getString(1);
					list2.add(s_name);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	

}
